﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;

namespace SMS.dao
{
    public class GradeDao
    {
        private static readonly GradeDao INSTANCE = new GradeDao();

        public static GradeDao Instance
        {
            get
            {
                return INSTANCE;
            }
        }

        private GradeDao() { }

        public DataTable findClazz(string blockID)
        {
            String sql = "select cla_name, cla_id from clazz where blo_id = @blockID order by cla_name";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blockID", int.Parse(blockID)));
            return DataFetcher.Instance.fetch();
        }

        public DataTable listBlock()
        {
            String sql = "select blo_id, blo_name from block order by blo_name";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public DataTable listCourse()
        {
            String sql = "select cor_id, cor_name from course order by cor_name";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public DataTable listSemester()
        {
            String sql = "select sem_id, sem_name from semester order by sem_name";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public DataTable listGradebyClass(int claID)
        {
            string sql = "select s.STU_FULLNAME, convert(varchar, s.STU_DOB, 103) as STU_DOB , g.GRA_FIFTEENMINTEST, g.GRA_SIXTYMINTEST, g.GRA_FINALTEST " +
                        " from GRADE g, REGISTRATION r, STUDENT s " +
                        " where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID and r.STU_ID = s.STU_ID " +
                        " and g.CLA_ID = @claID ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@claID", claID));
            return DataFetcher.Instance.fetch();
        }

        public DataTable listGradebyCourse(int claID, int corID)
        {
            string sql = "select s.STU_FULLNAME, convert(varchar, s.STU_DOB, 103) as STU_DOB , g.GRA_FIFTEENMINTEST, g.GRA_SIXTYMINTEST, g.GRA_FINALTEST " +
                        " from GRADE g, REGISTRATION r, STUDENT s " +
                        " where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID and r.STU_ID = s.STU_ID " +
                        " and g.CLA_ID = @claID and g.COR_ID = @corID ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@claID", claID));
            DataFetcher.Instance.addParameter(new SqlParameter("@corID", corID));
            return DataFetcher.Instance.fetch();
        }
        
        public DataTable listGradeAll(int claID, int corID, int semID)
        {
            string sql = "select s.STU_FULLNAME, convert(varchar, s.STU_DOB, 103) as STU_DOB , g.GRA_FIFTEENMINTEST, g.GRA_SIXTYMINTEST, g.GRA_FINALTEST " +
                        " from GRADE g, REGISTRATION r, STUDENT s " +
                        " where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID and r.STU_ID = s.STU_ID " +
                        " and g.CLA_ID = @claID and g.COR_ID = @corID and g.SEM_ID = @semID ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@claID", claID));
            DataFetcher.Instance.addParameter(new SqlParameter("@corID", corID));
            DataFetcher.Instance.addParameter(new SqlParameter("@semID", semID));
            return DataFetcher.Instance.fetch();
        }

        public DataTable listGradeRate(int corID, int semID)
        {
            string sql = "select c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) as CLA_COUNTED, COUNT(b.STU_ID) as CLA_PASSED, " +
                        " Cast (ROUND(((cast(COUNT(b.STU_ID) as float) / cast(isnull(a.counted, 0) as float))*100),1) as varchar) + ' %'  as Rate " +
                        " from CLAZZ c1 left join " +
                        " (select c2.CLA_ID, c2.CLA_NAME, count(r.STU_ID) as counted " + 
                        " from CLAZZ c2, REGISTRATION r  where c2.CLA_ID = r.CLA_ID  " +
                        " group by c2.CLA_ID, c2.CLA_NAME) as a " +
                        " on a.CLA_ID = c1.CLA_ID " +
                        " left join (select r.STU_ID, r.CLA_ID, ((g.GRA_FIFTEENMINTEST+g.GRA_FINALTEST+g.GRA_FINALTEST)/3) as DTB " +
                        " from GRADE g, REGISTRATION r where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID " +
                        " and g.COR_ID = @cor_id and g.SEM_ID = @sem_id ) as b " +
                        " on b.CLA_ID = c1.CLA_ID " +
                        " where b.DTB >=(select PRE_VALUE from PREFERENCE where PRE_CODE ='P004') " +
                        " group by c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) " +
                        " order by CLA_NAME ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@cor_id", corID));
            DataFetcher.Instance.addParameter(new SqlParameter("@sem_id", semID));
            return DataFetcher.Instance.fetch();
        }

        public DataTable listGradeRateByCourse(int corID)
        {
            string sql = "select c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) as CLA_COUNTED " +
                        " from CLAZZ c1 left join " +
                        " (select c2.CLA_ID, c2.CLA_NAME, count(r.STU_ID) as counted " +
                        " from CLAZZ c2, REGISTRATION r  where c2.CLA_ID = r.CLA_ID  " +
                        " group by c2.CLA_ID, c2.CLA_NAME) as a on a.CLA_ID = c1.CLA_ID " +
                        " left join (select r.STU_ID, r.CLA_ID " +
                        " from GRADE g, REGISTRATION r where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID " +
                        " and g.COR_ID = 1) as b on b.CLA_ID = c1.CLA_ID  " +
                        " group by c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) " +
                        " order by CLA_NAME ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@corID", corID));
            return DataFetcher.Instance.fetch();
        }

        public int insert(int studentId, int classId, int courseId, int semesterId, SqlDouble fifteenMinTest, SqlDouble sixtyMinTest, SqlDouble finalTest)
        {
            String sql = "insert into grade (stu_id, cla_id, cor_id, sem_id, gra_fifteenmintest, gra_sixtymintest, gra_finaltest) values " +
                            "(@stuId, @claId, @corId, @semId, @fifteenMinTest, @sixtyMinTest, @finalTest)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@stuId", studentId));
            DataFetcher.Instance.addParameter(new SqlParameter("@claId", classId));
            DataFetcher.Instance.addParameter(new SqlParameter("@corId", courseId));
            DataFetcher.Instance.addParameter(new SqlParameter("@semId", semesterId));
            DataFetcher.Instance.addParameter(new SqlParameter("@fifteenMinTest", fifteenMinTest));
            DataFetcher.Instance.addParameter(new SqlParameter("@sixtyMinTest", sixtyMinTest));
            DataFetcher.Instance.addParameter(new SqlParameter("@finalTest", finalTest));
            return DataFetcher.Instance.execute();
        }

		public DataTable listClassRateBySemester(int semID)
        {
            string sql = "select c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) as CLA_COUNTED, COUNT(tbhk.STU_ID) as CLA_PASSED, " +
                        " Cast (ROUND(((cast(COUNT(tbhk.STU_ID) as float) / cast(isnull(a.counted, 0) as float))*100),1) as varchar) + ' %'  as Rate " +
                        " from CLAZZ c1 left join " +
                        " (select c2.CLA_ID, c2.CLA_NAME, count(r.STU_ID) as counted " +
                        " from CLAZZ c2, REGISTRATION r  where c2.CLA_ID = r.CLA_ID  " +
                        " group by c2.CLA_ID, c2.CLA_NAME) as a  " +
                        " on a.CLA_ID = c1.CLA_ID " +
                        " left join (select tbm.SEM_ID, r.CLA_ID, r.STU_ID, ROUND(sum(tbm.DTB)/(select count(*) from COURSE), 1) as TBHK " +
                        " from REGISTRATION r " +
                        " left join(select g.SEM_ID, r.STU_ID, r.CLA_ID, g.COR_ID, " +
                        " ROUND(((g.GRA_FIFTEENMINTEST+g.GRA_FINALTEST+g.GRA_FINALTEST)/3), 1) as DTB " +
                        " from GRADE g, REGISTRATION r where g.STU_ID = r.STU_ID and g.CLA_ID = r.CLA_ID " +
                        " and g.SEM_ID = @semID) as tbm " +
                        " on tbm.CLA_ID = r.CLA_ID and tbm.STU_ID = r.STU_ID " +
                        " group by tbm.SEM_ID, r.CLA_ID, r.STU_ID) as tbhk " +
                        " on c1.CLA_ID = tbhk.CLA_ID " +
                        " where tbhk.TBHK >= (select PRE_VALUE from PREFERENCE where PRE_CODE ='P004') " +
                        " group by c1.CLA_ID, c1.CLA_NAME, isnull(a.counted, 0) ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@semID", semID));
            return DataFetcher.Instance.fetch();
        }
        public int edit(int gradeId, SqlDouble fifteenMinTest, SqlDouble sixtyMinTest, SqlDouble finalTest)
        {
            String sql = "update grade set gra_fifteenmintest = @fifteenMinTest, gra_sixtymintest = @sixtyMinTest, gra_finalTest = @finalTest where gra_id = @graId";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@fifteenMinTest", fifteenMinTest));
            DataFetcher.Instance.addParameter(new SqlParameter("@sixtyMinTest", sixtyMinTest));
            DataFetcher.Instance.addParameter(new SqlParameter("@finalTest", finalTest));
            DataFetcher.Instance.addParameter(new SqlParameter("@graId", gradeId));
            return DataFetcher.Instance.execute();
        }
    }
}
